***************************************************
******INSHEET buliding  data from BFS*******
****************************************************

*Import relevant data from BfS Building statistics

import delimited "$root/Data/Original/Table_EIN-01.txt", clear

*Rename and label the variables according to information in excel sheet
ren v1 EGID	
label var EGID "Eidg. Gebäudeidentifikator"	
rename v2 EDID
label var EDID "Eidg. Eingangsidentifikator"	
rename v3 EGAID
label var EGAID	"Eidg. Gebäudeadresseidentifikator"	
rename v4 ESID	
label var ESID "Eidg. Strassenidentifikator"
rename v5 STRINDX	
label var STRINDX "Strassenbezeichnung Index"	
rename v6 STRNAME
label var STRNAME "Strassenbezeichnung"	
rename v7 STRNAMK	
label var STRNAMK "Strassenbezeichnung kurz"	
rename v8 DEINR
label var DEINR	"Eingangsnummer Gebäude"
rename v9 STRSP	
label var STRSP"Strassenbezeichnung Sprache"	
rename v10 STROFFIZIEL
label var STROFFIZIEL "Strassenbezeichnung offiziell"	
rename v11 PLZ
label var PLZ "Postleitzahl"
rename v12 PLZZ
label var PLZZ "PLZ-Zusatzziffer"
rename v13 PLZNAME
label var PLZNAME "Ortsbezeichnung"
rename v14 DOFFADR
label var DOFFADR "Offizielle Adresse"
rename v15 DKODE
label var DKODE	"E-Koordinate"	
rename v16 DKODN 
label var DKODN "N-Koordinate"
rename v17 UPDATE_DATE	
label var UPDATE_DATE "Datum der letzten Änderung"	
rename v18 EXPORT_DATE
label var EXPORT_DATE "Datum des Exportes"

drop EGAID ESID STRINDX STRNAMK PLZZ STRSP STRINDX UPDATE_DATE EXPORT_DATE DOFFADR STROFFIZIEL
	
*The variables DKODE and DKODN need to be recoded into the old system of coordinates (LV03)
gen GKODX=substr(string(DKODE),2,6)
gen GKODY=substr(string(DKODN),2,6)

destring GKODX, force replace
destring GKODY, force replace

drop DKODN DKODE

label var EGID "Building identification number"	
label var EDID "Entrance idenficiation number"
label var STRNAME "Street name"
rename DEINR number 
label var number "House number"
label var PLZ "Zip code"
label var PLZNAME "City name" 
label var GKODX "X coordinate"
label var GKODY "Y coordinate"


*save the variables
sort EGID EDID
save "$root/Data/Original/entrance_2019.dta", replace


*Import Building text data
import delimited "$root/Data/Original/Table_GEB-02.txt", clear

local new "EGID	GEBNR	GBEZ	GDEKT	GDENR	GDENAME	GKODE	GKODN	GKSCE	GKAT	GKLAS	GSTAT	GBAUJ	GBAUM	GBAUP	GABBJ	GAREA	GVOL	GVOLNORM	GVOLSCE	GASTW	GANZWHG	GAZZI	GSCHUTZR	GEBF	GWAERZH1	GWAERZH2	GENH1	GENH2	GWAERSCEH1	GWAERSCEH2	GWAERDATH1	GWAERDATH2	GWAERZW1	GWAERZW2	GENW1	GENW2	GWAERSCEW1	GWAERSCEW2	GWAERDATW1	GWAERDATW2	GLOC1	GLOC2	GLOC3	GLOC4	GQUART	UPDATE_DATE	EXPORT_DATE"
ds
local old `r(varlist)'

rename (`old') (`new')

label var EGID "Eidg. Gebäudeidentifikator"	
label var GEBNR	"Amtliche Gebäudenummer"
label var GBEZ "Gebäudebezeichnung" 
label var GDEKT	 "Kantonskürzel"	
label var GDENR "BFS-Gemeindenummer"
label var GDENAME "Gemeindename"	
label var GKODE "E-Koordinate"
label var GKODN "N-Koordinate"
label var GKSCE "Koordinatenherkunft"
label var GKAT "Gebäudekategorie"
label var GKLAS "Gebäudeklasse"
label var GSTAT "Gebäudestatus"	
label var GBAUJ "Baujahr des Gebäudes"
label var GBAUM "Baumonat des Gebäudes"
label var GBAUP	"Bauperiode"	
label var GABBJ "Abbruchjahr des Gebäudes"
label var GAREA	"Gebäudefläche"	
label var GVOL "Gebäudevolumen"
label var GVOLNORM "Gebäudevolumen: Norm"	
label var GVOLSCE "Informationsquelle zum Gebäudevolumen"
label var GASTW	"Anzahl Geschosse"	
label var GANZWHG "Anzahl Wohnungen"	
label var GAZZI	"Anzahl separate Wohnräume"	
label var GSCHUTZR "Zivilschutzraum"
label var GEBF "Energiebezugsfläche" 	
label var GWAERZH1 "Wärmeerzeuger Heizung 1"
label var GWAERZH2 "Wärmeerzeuger Heizung 2"	
label var GENH1	"Energie-/Wärmequelle Heizung 1"
label var GENH2 "Energie-/Wärmequelle Heizung  2"	
label var GWAERSCEH1 "Informationsquelle Heizung 1"	
label var GWAERSCEH2 "Informationsquelle Heizung 2"	
label var GWAERDATH1 "Aktualisierungsdatum Heizung 1"	
label var GWAERDATH2 "Aktualisierungsdatum Heizung 2"	
label var GWAERZW1 "Wärmeerzeuger Warmwasser 1"	
label var GWAERZW2 "Wärmeerzeuger Warmwasser 2"	
label var GENW1	"Energie-/Wärmequelle Warmwasser 1"	
label var GENW2	"Energie-/Wärmequelle Warmwasser  2"	
label var GWAERSCEW1 "Informationsquelle Warmwasser 1"		
label var GWAERSCEW2 "Informationsquelle Warmwasser 2"	
label var GWAERDATW1 "Aktualisierungsdatum Warmwasser 1"
label var GWAERDATW2 "Aktualisierungsdatum Warmwasser 2"	
label var GLOC1 "Lokalcodes 1"
label var GLOC2 "Lokalcodes 2"	
label var GLOC3 "Lokalcodes 3" 	
label var GLOC4 "Lokalcodes 4"
label var GQUART "Quartier"
label var UPDATE_DATE "Datum der letzten Änderung"	
label var EXPORT_DATE "Datum des Exportes"	

drop UPDATE_DATE EXPORT_DATE GLOC* GKODE GKODN GKSCE GQUART GVOL GVOLNORM GVOLSCE GBEZ GEBNR GSCHUTZR GEBF 

*based on GKAT and GKLAS we construct two variables (category and class)
gen build_cat=""
replace build_cat="Provisional" if GKAT==1010
replace build_cat="Residential (pure)" if GKAT==1020
replace build_cat="Resdential (with insignificant other purpose)" if GKAT==1030
replace build_cat="Partially residential" if GKAT==1040
replace build_cat="non-residential" if GKAT==1060
replace build_cat="special purpose" if GKAT==1080

gen build_class="other"
replace build_class="1 apartment" if GKLAS==1110
replace build_class="2 apartments" if GKLAS==1121
replace build_class="3+ apartments" if GKLAS==1122
replace build_class="multi-purpose / community building" if GKLAS==1130

**Create variables for heat_system heat_product and water_product based on the underlying variables:

*reproduce the information of GWAERZH2 if GWAERZH1 is empty
replace GWAERZH1=GWAERZH2 if GWAERZH1==.
replace GWAERZH1=GWAERZH2 if GWAERZH1==7400 & GWAERZH2!=7400 & GWAERZH2!=.

recode GWAERZH1 (7400=1) (7410=2) (7411 = 2) (7420 = 3) (7421 =3) (7430= 4) (7431= 4) (7432= 4) (7433= 4) (7434 =4) (7435= 4) (7436= 5) (7440= 6) (7441= 6) (7450= 7) (7451= 7) (7452= 7) (7460= 8) (7461= 8) (7499= 99)
recode GWAERZH2 (7400=1) (7410=2) (7411 = 2) (7420 = 3) (7421 =3) (7430= 4) (7431= 4) (7432= 4) (7433= 4) (7434 =4) (7435= 4) (7436= 5) (7440= 6) (7441= 6) (7450= 7) (7451= 7) (7452= 7) (7460= 8) (7461= 8) (7499= 99)
gen equal=.
replace equal=1 if GWAERZH1==GWAERZH2 & GWAERZH2!=.
replace equal=0 if GWAERZH1!=GWAERZH2 & GWAERZH2!=.


gen heat_sys=.
replace heat_sys=GWAERZH1 if GWAERZH2==.
replace heat_sys=GWAERZH1 if equal==1
replace heat_sys=GWAERZH1 if equal==0 & GWAERZH2==1
replace heat_sys=9 if equal==0 & GWAERZH2!=1 & GWAERZH2!=.

label define heating_system 1 "none" 2 "Heat pump" 3 "Solarpanel" 4 "Boiler" 5 "Oven" 6 "Heat power coupling" 7 "Electro" 8 "Heat exchanger" 9 "multiple" 99 "other"

label values heat_sys heating_system

drop equal GWAERZH1 GWAERZH2

*Create a variable for the resource that is used for the heating
replace GENH1=GENH2 if GENH1==.
replace GENH1=GENH2 if GENH1==7500 & GENH2!=7500

recode GENH1 (7500=1) (7501=2) (7510=3) (7511=3) (7512=3) (7513=3) (7520=4) (7530=5) (7540=6) (7541=6) (7542=6) (7543=6) (7550=7) (7560=8) (7570=9) (7580=10) (7581=10) (7582=10) (7598=99) (7599=999)
recode GENH2 (7500=1) (7501=2) (7510=3) (7511=3) (7512=3) (7513=3) (7520=4) (7530=5) (7540=6) (7541=6) (7542=6) (7543=6) (7550=7) (7560=8) (7570=9) (7580=10) (7581=10) (7582=10) (7598=99) (7599=999)
gen equal=.
replace equal=1 if GENH1==GENH2 & GENH2!=.
replace equal=0 if GENH1!=GENH2 & GENH2!=.

label define heating_resource 1 "none" 2 "Air" 3 "Geothermal & Water" 4 "Gas" 5 "Oil" 6 "Wood" 7 "Waste heat" 8 "Electricity" 9 "Sun" 10 "Distance heat" 11 "multiple" 99 "undefined" 999 "other"

gen heat_res=.
replace heat_res=GENH1 if GENH2==.
replace heat_res=GENH1 if equal==1
replace heat_res=GENH1 if equal==0 & GENH2==1
replace heat_res=11 if equal==0 & GENH2!=1 & GENH2!=.

label values heat_res heating_resource

drop equal GENH1 GENH2 GWAERSCEH1 GWAERSCEH2 GWAERDATH1 GWAERDATH2


*Create a variable for the system that is used for warmwater production
 replace GWAERZW1=GWAERZW2 if GWAERZW1==.
 replace GWAERZW1=GWAERZW2 if GWAERZW1==7600
 
recode GWAERZW1 (7600=1) (7610=2) (7620=3) (7630=4) (7632=4) (7634=4) (7640=5) (7650=6) (7651=6) (7660=7) (7699=99)
recode GWAERZW2 (7600=1) (7610=2) (7620=3) (7630=4) (7632=4) (7634=4) (7640=5) (7650=6) (7651=6) (7660=7) (7699=99)

gen equal=.
replace equal=1 if GWAERZW1==GWAERZW2 & GWAERZW2!=.
replace equal=0 if GWAERZW1!=GWAERZW2 & GWAERZW2!=.

gen hotwater_sys=.
replace hotwater_sys=GWAERZW1 if GWAERZW2==.
replace hotwater_sys=GWAERZW1 if equal==1
replace hotwater_sys=GWAERZW1 if equal==0 & GWAERZW2==1
replace hotwater_sys=9 if equal==0 & GWAERZW2!=1 & GWAERZW2!=.

label define water_system 1 "none" 2 "Heat pump" 3 "Solarpanel" 4 "Boiler" 5 "Heat power coupling" 6 "Electro boiler" 7 "Heat exchanger" 9 "multiple" 99 "other"

label values hotwater_sys water_system

drop GWAERZW1 GWAERZW2 equal

*Create a variable for the resource that is used for hotwater production
replace GENW1=GENW2 if GENW1==.
replace GENW1=GENW2 if GENW1==7500 & GENW2!=7500

recode GENW1 (7500=1) (7501=2) (7510=3) (7511=3) (7512=3) (7513=3) (7520=4) (7530=5) (7540=6) (7541=6) (7542=6) (7543=6) (7550=7) (7560=8) (7570=9) (7580=10) (7581=10) (7582=10) (7598=99) (7599=999)
recode GENW2 (7500=1) (7501=2) (7510=3) (7511=3) (7512=3) (7513=3) (7520=4) (7530=5) (7540=6) (7541=6) (7542=6) (7543=6) (7550=7) (7560=8) (7570=9) (7580=10) (7581=10) (7582=10) (7598=99) (7599=999)
gen equal=.
replace equal=1 if GENW1==GENW2 & GENW2!=.
replace equal=0 if GENW1!=GENW2 & GENW2!=.

label define water_resource 1 "none" 2 "Air" 3 "Geothermal & Water" 4 "Gas" 5 "Oil" 6 "Wood" 7 "Waste heat" 8 "Electricity" 9 "Sun" 10 "Distance heat" 11 "multiple" 99 "undefined" 999 "other"

gen hotwater_res=.
replace hotwater_res=GENW1 if GENW2==.
replace hotwater_res=GENW1 if equal==1
replace hotwater_res=GENW1 if equal==0 & GENW2==1
replace hotwater_res=11 if equal==0 & GENW2!=1 & GENW2!=.

label values hotwater_res water_resource

drop equal GENW1 GENW2 GWAERSCEW1 GWAERSCEW2 GWAERDATW1 GWAERDATW2

drop GDEKT 

*Label the additionally constructed variables and translate the non-english ones
label var EGID "Building identification number"	
label var GDENAME "Community name"	
label var GKAT "Building category (numerical)"
label var build_cat "Building category (string)"
label var GKLAS "Building class (numeric)"
label var build_class "Building class (string)"
label var GSTAT "Building Status"	
label var GBAUJ "Year of construction"
label var GBAUM "Month of construction"
label var GBAUP	"Period of construction"	
label var GABBJ "Year of destruction"
label var GAREA	"Surface area"	
label var GASTW	"Number of floors"	
label var GANZWHG "Number of apartments"	
label var GAZZI	"Number of additional living spaces"	
label var heat_sys "System of heating"
label var heat_res "Resource of heating"
label var hotwater_sys "System of hotwater production"
label var hotwater_res "Resource of hotwater production"	

sort EGID
save "$root/Data/Original/building_char2019.dta", replace

* Now import the information about apartments
import delimited "$root/Data/Original/Table_WHG-02.txt", clear

local new "EGID	EDID	EWID	WHGNR	WEINR	WSTWK	WBEZ	WBAUJ	WABBJ	WSTAT	WAREA	WAZIM	WKCHE	WMEHRG	UPDATE_DATE	EXPORT_DATE"

ds
local old `r(varlist)'

rename (`old') (`new')

drop UPDATE_DATE EXPORT_DATE WBAUJ WABBJ 



label var EWID "Apartment identification number "
label var WSTWK "Apartment floor"
label var WBEZ  "Apartment number (on floor)"
label var WHGNR "administrative apartment number"
label var WEINR "real apartment number"
label var WMEHRG "multiple floor apartment"
label var WKCHE "apartment has kitchen"

drop WKCHE

collapse (mean) WAZIM WAREA (sum) tot_area=WAREA tot_room=WAZIM WMEHRG (count) tot_app=EWID, by(EGID EDID)

label var EGID "Building identification number"
label var EDID "Entrance identification number "
label var WAZIM "average number of rooms"
label var WAREA "average apartment surface"
label var WMEHRG "number of maisonette apartments"



sort EGID EDID
save "$root/Data/Original/apartment_char2019.dta", replace




